/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import model.entities.Course_ShiftDetails;
import model.entities.Shift;
import model.entities.Course;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.Course_ShiftDetailDAOService;

/**
 *
 * @author Luffy
 */
public class Course_ShiftDetailsDAO implements Course_ShiftDetailDAOService {
    private static Course_ShiftDetailsDAO course_ShiftDetailsDAO;
    
    public static Course_ShiftDetailsDAO getInstance(){
        if(course_ShiftDetailsDAO ==null){
            course_ShiftDetailsDAO= new Course_ShiftDetailsDAO();
        }
        return course_ShiftDetailsDAO; 
    }
    
    @Override
     public List<Course_ShiftDetails> getCourse_ShiftDetailsList(){
         ArrayList<Course_ShiftDetails> listCSD = new ArrayList<Course_ShiftDetails>();
         Connection conn =ConnectionFactory.getConnection();
         String sql="select * from tblStudent_CourseDetails";
         PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs= pstmt.executeQuery();
            while(rs.next()){
             Course_ShiftDetails csd = new Course_ShiftDetails();
             csd.setCourse_ShiftDetailID(rs.getInt("Course_ShiftDetailID"));
             Course cl = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
             Shift shift = ShiftDAO.getInstance().getShiftByID(rs.getInt("ShiftID"));
             csd.setCourse(cl);
             csd.setShift(shift);
             csd.setDay(rs.getInt("Day"));
             listCSD.add(csd);
         }
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
         
        return listCSD;
     }   
     
    @Override
     public Course_ShiftDetails getCourse_ShiftDetailsByID(int course_ShiftDetailsID){
        Course_ShiftDetails csd = new Course_ShiftDetails();
        Connection conn = ConnectionFactory.getConnection();
        String sql= "select * from tblStudent_CourseDetails where Course_ShiftDetailsID = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, course_ShiftDetailsID);
            ResultSet rs = pstmt.executeQuery();
        
            csd.setCourse_ShiftDetailID(rs.getInt("Course_ShiftDetailsID"));
             Course cl = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
             Shift shift = ShiftDAO.getInstance().getShiftByID(rs.getInt("ShiftID"));
             csd.setCourse(cl);
             csd.setShift(shift);
             csd.setDay(rs.getInt("Day")); 
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }     
        return csd;
     }

    @Override
    public boolean insertCourse_ShiftDetails(Course_ShiftDetails CSD) {
        Connection conn = ConnectionFactory.getConnection();
        String query = "INSERT INTO tblCourse_ShiftDetails VALUES(?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, CSD.getCourse().getCourseID());
            pstmt.setInt(2, CSD.getShift().getShiftID());
            pstmt.setInt(3, CSD.getDay());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
    }

    @Override
    public boolean updateCourse_ShiftDetails(Course_ShiftDetails CSD) {
        Connection conn = ConnectionFactory.getConnection();
        String query = "UPDATE tblCourse_ShiftDetails SET CourseID=?,ShiftID=?,[Day]=?  WHERE Course_ShiftDetailID=?";
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, CSD.getCourse().getCourseID());
            pstmt.setInt(2, CSD.getShift().getShiftID());
            pstmt.setInt(3, CSD.getDay());
            pstmt.setInt(4, CSD.getCourse_ShiftDetailID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
    }

    @Override
    public boolean deleteCourse_ShiftDetails(int id) {
        Connection conn = ConnectionFactory.getConnection();
        String query = "DELETE tblCourse_ShiftDetails WHERE Course_ShiftDetailID=?";
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
    }

    @Override
    public List<Course_ShiftDetails> findCourse_ShiftDetailsByID(int id) {
        List<Course_ShiftDetails> lstCSD = new ArrayList<Course_ShiftDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse_ShiftDetails WHERE Course_ShiftDetailID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course_ShiftDetails CSD = new Course_ShiftDetails();
                CSD.setCourse_ShiftDetailID(rs.getInt("Course_ShiftDetailID"));
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                CSD.setCourse(c);
                Shift sh = ShiftDAO.getInstance().getShiftByID(rs.getInt("ShiftID"));
                CSD.setShift(sh);
                CSD.setDay(rs.getInt("Day"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstCSD;
    }

    @Override
    public List<Course_ShiftDetails> findCourse_ShiftDetailsByDay(int dayOfWeek) {
        List<Course_ShiftDetails> lstCSD = new ArrayList<Course_ShiftDetails>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblCourse_ShiftDetails WHERE [Day]=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, dayOfWeek);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Course_ShiftDetails CSD = new Course_ShiftDetails();
                CSD.setCourse_ShiftDetailID(rs.getInt("Course_ShiftDetailID"));
                Course c = CourseDAO.getInstance().getCourseByID(rs.getInt("CourseID"));
                CSD.setCourse(c);
                Shift sh = ShiftDAO.getInstance().getShiftByID(rs.getInt("ShiftID"));
                CSD.setShift(sh);
                CSD.setDay(rs.getInt("Day"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(Course_ShiftDetailsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstCSD;
    }

    @Override
    public List<Course> getCourseByShiftID(int shiftID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Shift> getShiftByCourseID(int courseID) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}
